package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.glowmee.cmd.ThirdCategoryCMD;

/** 3차카테고리의 기능을 구현하는 클래스 */
public class ThirdCategoryDAO {
	
	static Logger logger = Logger.getLogger(ThirdCategoryDAO.class);
	
	private static ThirdCategoryDAO instance = null;
	
	DBHandler db = null;
	
	public static ThirdCategoryDAO getInstance() {
		if (instance == null) {
			instance = new ThirdCategoryDAO();
		}
		return instance;
	}
	
	public ThirdCategoryDAO() {
		db = DBHandler.getInstance();
	}

	/**
	 * 3차카테고리 추가
	 * @param idSecondCategory		- 2차카테고리 항번
	 * @param thirdCategoryText		- 3차카테고리 이름
	 * @param sortKey				- 정렬순서
	 * @param isDisplay				- 전시여부
	 * @return int					- 추가된 3차카테고리에 대한 일련번호를 리턴
	 */
	public int insertThirdCategory(ThirdCategoryCMD cmd) throws Exception {
		logger.debug("[insertThirdCategory] ---------- Method Start ----------");
		logger.debug("[insertThirdCategory][PARAM] idSecondCategory=" + cmd.getIdSecondCategory());
		logger.debug("[insertThirdCategory][PARAM] thirdCategoryText=" + cmd.getThirdCategoryText());
		logger.debug("[insertThirdCategory][PARAM] sortKey=" + cmd.getSortKey());
		logger.debug("[insertThirdCategory][PARAM] isDisplay=" + cmd.getIsDisplay());
		
		int result = 0;
		
		String sql = "INSERT INTO thirdcategory("
				+ "idSecondCategory, thirdCategoryText, sortKey, isDisplay, create_date"
				+ ") VALUES (?, ?, ?, ?, ?);";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdSecondCategory());
			pstmt.setString(2, cmd.getThirdCategoryText());
			pstmt.setInt(3, cmd.getSortKey());
			pstmt.setInt(4, cmd.getIsDisplay());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			
			DBHandler.getInstance().beginTrans();
			int count = pstmt.executeUpdate();
			
			if (count > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertThirdCategory] " + e.getLocalizedMessage());
			logger.error("[insertThirdCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertThirdCategory][RETURN] result=" + result);
		logger.debug("[insertThirdCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 3차카테고리 수정
	 * @param idThirdCategory		- 3차카테고리 항번
	 * @param idSecondCategory		- 2차카테고리 항번
	 * @param thirdCategoryText		- 3차카테고리 이름
	 * @param sortKey				- 정렬순서
	 * @param isDisplay				- 전시여부
	 * @return int					- 수정된 3차카테고리 갯수를 리턴
	 */
	public int updateThirdCategory(ThirdCategoryCMD cmd) throws Exception {
		logger.debug("[updateThirdCategory] ---------- Method Start ----------");
		logger.debug("[updateThirdCategory][PARAM] idThirdCategory=" + cmd.getIdThirdCategory());
		logger.debug("[insertThirdCategory][PARAM] idSecondCategory=" + cmd.getIdSecondCategory());
		logger.debug("[insertThirdCategory][PARAM] thirdCategoryText=" + cmd.getThirdCategoryText());
		logger.debug("[insertThirdCategory][PARAM] sortKey=" + cmd.getSortKey());
		logger.debug("[insertThirdCategory][PARAM] isDisplay=" + cmd.getIsDisplay());
		
		int result = 0;
		
		String sql = "UPDATE thirdcategory "
				+ "SET idSecondCategory=?, thirdCategoryText=?, sortKey=?, isDisplay=?, modified_date=? "
				+ "WHERE idThirdCategory=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdSecondCategory());
			pstmt.setString(2, cmd.getThirdCategoryText());
			pstmt.setInt(3, cmd.getSortKey());
			pstmt.setInt(4, cmd.getIsDisplay());
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(6, cmd.getIdThirdCategory());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateThirdCategory] " + e.getLocalizedMessage());
			logger.error("[updateThirdCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateThirdCategory][RETURN] result=" + result);
		logger.debug("[updateThirdCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 3차카테고리 삭제
	 * @param idThirdCategory		- 3차카테고리 항번
	 * @return int					- 삭제된 3차카테고리 갯수를리턴
	 */
	public int deleteThirdCategory(int idThirdCategory) throws Exception {
		logger.debug("[deleteThirdCategory] ---------- Method Start ----------");
		logger.debug("[deleteThirdCategory][PARAM] idThirdCategory=" + idThirdCategory);
		
		int result = 0;
		
		String sql = "DELETE FROM thirdcategory "
				+ "WHERE idThirdCategory=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idThirdCategory);
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[deleteThirdCategory] " + e.getLocalizedMessage());
			logger.error("[deleteThirdCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[deleteThirdCategory][RETURN] result=" + result);
		logger.debug("[deleteThirdCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 3차카테고리 조회
	 * @param idThirdCategory		- 3차카테고리 항번
	 * @return ThirdCategoryCMD		- 3차카테고리 객체로 리턴
	 */
	public ThirdCategoryCMD getThirdCategory(int idThirdCategory) throws Exception {
		logger.debug("[getThirdCategory] ---------- Method Start ----------");
		logger.debug("[getThirdCategory][PARAM] idThirdCategory=" + idThirdCategory);
		
		ThirdCategoryCMD result = null;
		
		String sql = "SELECT idThirdCategory, idSecondCategory, thirdCategoryText, sortKey, isDisplay, create_date, modified_date "
				+ "FROM thirdcategory WHERE idThirdCategory=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idThirdCategory);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int idSecondCategory = rs.getInt(2);
					String thirdCategoryText = rs.getString(3);
					int sortKey = rs.getInt(4);
					int isDisplay = rs.getInt(5);
					String create_date = rs.getString(6);
					String modified_date = rs.getString(7);
					result = new ThirdCategoryCMD(num, idSecondCategory, thirdCategoryText, sortKey, isDisplay, create_date, modified_date);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getThirdCategory] " + e.getLocalizedMessage());
			logger.error("[getThirdCategory] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getThirdCategory][RETURN] result=" + result.toString());
		}
		logger.debug("[getThirdCategory] ---------- Method End ----------");
		
		return result;
	}

	/**
	 * 3차카테고리 리스트 조회
	 * @param page		- 페이징처리를 위한 시작페이지번호
	 * @param total		- 페이징처리를 위한 한페이당 출력수
	 * @return			- 전체 3차카테고리를 ArrayList로 리턴
	 */
	public ArrayList<ThirdCategoryCMD> getThirdCategoryList(int page, int count, String where) throws Exception {
		logger.debug("[getThirdCategoryList] ---------- Method Start ----------");
		logger.debug("[getThirdCategoryList][PARAM] page=" + page);
		logger.debug("[getThirdCategoryList][PARAM] count=" + count);
		
		ArrayList<ThirdCategoryCMD> result = null;
		
		int cnt = db.getCount("thirdcategory", "idThirdCategory", where);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql;
		
		if (where == null) {
			sql = "SELECT idThirdCategory, idSecondCategory, thirdCategoryText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM thirdcategory "
					+ "LIMIT ?, ?";
		} else {
			sql = "SELECT idThirdCategory, idSecondCategory, thirdCategoryText, sortKey, isDisplay, create_date, modified_date "
					+ "FROM thirdcategory "
					+ "WHERE " + where + " "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ThirdCategoryCMD>();
				
				while(rs.next()) {
					int num = rs.getInt(1);
					int idSecondCategory = rs.getInt(2);
					String thirdCategoryText = rs.getString(3);
					int sortKey = rs.getInt(4);
					int isDisplay = rs.getInt(5);
					String create_date = rs.getString(6);
					String modified_date = rs.getString(7);
					ThirdCategoryCMD item = new ThirdCategoryCMD(num, idSecondCategory, thirdCategoryText, sortKey, isDisplay, create_date, modified_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getThirdCategoryList] " + e.getLocalizedMessage());
			logger.error("[getThirdCategoryList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getThirdCategoryList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getThirdCategoryList] ---------- Method End ----------");
		
		return result;
	}
	
}
